package john.walker.analyse;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.io.IOUtils;
import org.h2.jdbcx.JdbcConnectionPool;

/**
 *
 * 日志分析，存储到数据库
 *
 * @author Johnnie Walker
 * @date 2017年4月13日
 */
public class LogAnalyser {

	/**
	 * 建表
	 */
	protected final static String DDL = "create table if not exists dbtrace_log ( "
			+ "id int not null auto_increment, "
			+ "hash varchar(32) not null default '', "
			+ "time long not null default 0, "
			+ "sql text, "
			+ "args text, "
			+ "trace text, "
			+ "start_time timestamp, "
			+ "primary key(id) "
			+ ")";

	/**
	 * 插入日志
	 */
	protected final static String DML = "insert into dbtrace_log (hash, time, sql, args, trace, start_time) values(?, ?, ?, ?, ?, ?)" ;

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		JdbcConnectionPool pool = JdbcConnectionPool.create("jdbc:h2:~/h2logdb", "sa", "sa");
		pool.setLoginTimeout(5);
		pool.setMaxConnections(2);

		try {
			// 建表
			Connection con = pool.getConnection();
			PreparedStatement stmt = con.prepareStatement(DDL);
			stmt.execute();
			stmt.close();

			// 清空日志
			stmt = con.prepareStatement("truncate table dbtrace_log");
			stmt.execute();
			stmt.close();
			con.close();

			// 日志解析
			File file = new File("logs/dbtrace.log");
			BufferedReader reader = IOUtils.buffer(new InputStreamReader(new FileInputStream(file), "utf-8"));
			String line = null;
			while((line = reader.readLine()) != null) {
				line = line.trim();
				if(line.startsWith("SQL代理：")) {
					String sql = line.substring("SQL代理：".length()).trim();

					line = reader.readLine().trim();
					String sqlArgs = line.substring("SQL参数：".length()).trim();

					line = reader.readLine().trim();
					String sqlTimeStr = line.substring("SQL耗时：".length()).trim();
					long sqlTime = 0L;
					if(sqlTimeStr.endsWith("毫秒")) {
						sqlTime = Long.valueOf(sqlTimeStr.replace("毫秒", ""));
					} else if(sqlTimeStr.endsWith("秒")) {
						sqlTime = Long.valueOf(sqlTimeStr.replace("秒", "")) * 1000;
					} else if(sqlTimeStr.endsWith("分钟")) {
						sqlTime = Long.valueOf(sqlTimeStr.replace("分钟", "")) * 60000;
					} else if(sqlTimeStr.endsWith("小时")) {
						sqlTime = Long.valueOf(sqlTimeStr.replace("小时", "")) * 3600000;
					}

					reader.readLine();
					StringBuffer buffer = new StringBuffer();
					while((line = reader.readLine()) != null) {
						if(line.trim().isEmpty()) {
							break;
						}
						buffer.append(line.trim()).append("\n");
					}
					String trace = buffer.toString();

					String hash = DigestUtils.md5Hex(sql);

					// 保存纪录
					con = pool.getConnection();
					stmt = con.prepareStatement(DML);

					stmt.setString(1, hash);
					stmt.setLong(2, sqlTime);
					stmt.setString(3, sql);
					stmt.setString(4, sqlArgs);
					stmt.setString(5, trace);
					stmt.setString(6, null);
					stmt.execute();

					stmt.close();
					con.close();
				}
			}

			// 查询日志
			con = pool.getConnection();
			stmt = con.prepareStatement("select * from dbtrace_log order by time desc limit 10");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt("id")
						+ ", " + rs.getString("hash")
						+ ", " + rs.getLong("time")
						+ ", " + clob2String(rs.getClob("sql"))
						+ ", " + clob2String(rs.getClob("args"))
						+ ", " + clob2String(rs.getClob("trace"))
						+ ", " + rs.getTimestamp("start_time")
						);
			}

			rs.close();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pool.dispose();
		}
	}

	/**
	 * @param clob
	 * @return
	 */
	private static String clob2String(Clob clob) {
		if(clob == null) {
			return null;
		}

		try {
			return clob.getSubString(1, (int) clob.length());
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

}
